ANALYTICS ENGINEERING / AVIATION CUSTOMER EXPERIENCE
AeroVoice turns public aviation reviews — airlines, airports, lounges, seats — into an audited dimensional warehouse with NLP enrichment and a privacy-safe public dashboard, built to answer real stakeholder questions with numbers that can be traced back to tests.
01 / CONTEXT & PROBLEM
Public aviation reviews mix four different things — airline flights, airport experiences, lounges, and seats — each with its own rating structure, categorical quirks, and sample-size traps. Naively averaging them produces confident-looking numbers that mislead: a "top airline" ranking built on 12 reviews, or sentiment scores leaking personally identifiable review text.
The goal: a warehouse where each review type keeps its own grain, metrics are defined once with guardrails, and the public dashboard only ever exposes privacy-safe aggregates — then prove it works by answering 15 concrete stakeholder requests.
02 / ARCHITECTURE
Raw review data
airline · airport · lounge · seat
→
Python ingestion + NLP
local sentiment · topic signals · no external APIs
→
dbt on SQL Server: stg → atomic facts → semantic marts
39 models · conformed dimensions · 4 review grains kept separate
→
Quality gates
95 dbt tests · 25 Python contract/unit tests · CI
→
ECharts dashboard
privacy-safe aggregates · GitHub Pages
Airflow provides an audited refresh entrypoint · GitHub Actions runs quality gates on every change
03 / KEY DECISIONS
Four separate review grains, not one merged table
Airline, airport, lounge and seat reviews measure different experiences. Keeping atomic facts per grain with conformed dimensions prevents cross-grain averages that look right and mean nothing.
Sample-size guardrails inside metric definitions
Recommendation rate and low-rating rate only surface when an entity clears a minimum review count — defined in the semantic marts, so no downstream chart can rank an airline on a handful of reviews.
Local NLP, zero external calls
Sentiment and topic enrichment run locally in Python — reproducible, free, and no review text ever leaves the machine. The public layer publishes aggregates only.
Stakeholder requests as the acceptance test
15 concrete questions (rankings, drivers of dissatisfaction, coverage) defined the marts — the warehouse was done when every request could be answered from tested models, not when the schema looked elegant.
04 / RESULTS
05 / WHAT I'D DO DIFFERENTLY
I wrote several staging models before locking metric definitions, and reworked them once guardrails changed what the marts needed. Next time: agree the governed metrics and their minimum-sample rules first, then let them dictate the model DAG.
I'd also add incremental loading — the full-refresh pattern is fine at 214K reviews but wouldn't survive a continuously growing source.